package com.qianfeng.shixu.dao;

import com.mysql.jdbc.Driver;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.*;

public class JDBCTest {
    private Connection connection;
//    抽取Statement对象,让多个方法共享
    private Statement statement;

    /*
    如果让每个单元测试执行之前,都要初始化代码,那么把初始化的代码放入@Before修饰的方法中
     */
@Before
public void before() throws SQLException, ClassNotFoundException {
    //1. 注册驱动,连接数据库第一步
    DriverManager.registerDriver(new Driver());  //不推荐
    //实际注册驱动
    Class.forName("com.mysql.jdbc.Driver");
    //2.得到数据库连接Connection,它是Java程序连接数据库的通道
     connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/wordmanager", "root", "");
    System.out.println("connection = " + connection);
    //3 得到Statement对象
     statement = connection.createStatement();
}

@After
public void after(){

//        5.关闭statement对象
    try {
        statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

//        6.关闭Connection对象
    try {
        connection.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
////        4.执行sql语句
//        statement.executeUpdate("update word set comm='调试' where id=8");

    }
    /*
    使用单元测试演示表的插入
     */
    @Test
    public void testInsert() throws SQLException, ClassNotFoundException {
        String sql = "insert into word(id,english,chinese,comm) values (11,'person','人','仅供测试')";
        executeSql(sql);

    }

    private void executeSql(String sql) throws SQLException {
        statement.executeUpdate(sql);
    }

    @Test
    public void testDelete() throws SQLException {
        String sql = "delete word set comm='调试' where id=8";
        executeSql(sql);

    }

    @Test
    public void testSelect() throws SQLException {
//        通过executeQuery得到结果集ResultSet
        ResultSet resultSet = statement.executeQuery("select * from word");
//       遍历当前表中所有的值,直到遍历完成
        while ( resultSet.next()) {
            System.out.println(resultSet.getObject(1));
            System.out.println(resultSet.getObject(2));
            System.out.println(resultSet.getObject(3));
            System.out.println(resultSet.getObject(4));
            System.out.println(" ================================= ");
        }

    }
    //需求,查询某个单词的翻译,如果有就返回中文释义,如果没有就返回为null
    @Test
    public void testSelectByEnglish() throws SQLException {
        String str = "english";
            ResultSet resultSet = statement.executeQuery("select * from word where english='"+str+"'");

        if (resultSet.next()) {
            System.out.println("查到结果为:" + resultSet.getObject("chinese"));
        } else {
            System.out.println("没有查到");
        }
    }

    @Test
    public void testPrepareStatement() throws SQLException {
        String str = "cat";
//        使用预编译Statemwent来进行查询,传参时候不用字符串拼接,使用?占位符
        PreparedStatement preparedStatement = connection.prepareStatement("select * from word where english=?");
        preparedStatement.setObject(1,str);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            System.out.println("查到结果为:" + resultSet.getObject("chinese"));
        } else {
            System.out.println("没有查到");
        }
    }
}